# _*_ coding:utf-8 _*_
# @File  : receipt.py
# @Time  : 2020-09-24 13:26
# @Author: zizle
""" 仓库仓单数据
API-1: 仓单数据保存
"""
from typing import List
from datetime import datetime

import pandas as pd
from fastapi import APIRouter, Depends, Body, HTTPException, Query
from fastapi.encoders import jsonable_encoder
from utils.verify import oauth2_scheme, decipher_user_token
from db.mysql_z import MySqlZ
from .models import ReceiptItem
receipt_router = APIRouter()

# --------------- 以下接口即将废弃

def verify_date(today: str = Query(...)):
    try:
        datetime.strptime(today, "%Y%m%d")
    except Exception:
        raise HTTPException(status_code=400, detail="Param today format: %Y%m%d")
    return today


@receipt_router.post("/delivery/receipt/", summary="保存仓单数据", status_code=201)
async def save_delivery_receipt(
        user_token: str = Depends(oauth2_scheme),
        receipt_item: List[ReceiptItem] = Body(...)
):
    user_id, _ = decipher_user_token(user_token)
    if not user_id:
        raise HTTPException(status_code=401, detail="UnAuthorization")
    with MySqlZ() as cursor:
        cursor.execute(
            "SELECT id, role FROM user_user WHERE id=%s;", (user_id, )
        )
        user_role = cursor.fetchone()
        if not user_role or user_role["role"] not in ["superuser", "operator"]:
            raise HTTPException(status_code=401, detail="UnAuthorization")
        # 查询今日仓单是否已经存在,存在则不添加
        today = receipt_item[0].date
        cursor.execute("SELECT `date` FROM delivery_warehouse_receipt WHERE `date`=%s;", (today, ))
        if cursor.fetchone():
            raise HTTPException(status_code=403, detail="Today Receipts Exist.")
        # 保存数据到数据库
        save_count = cursor.executemany(
            "INSERT INTO delivery_warehouse_receipt (warehouse_code,warehouse_name,variety,"
            "variety_en,`date`,receipt,increase) VALUES (%(warehouse_code)s,%(warehouse_name)s,%(variety)s,"
            "%(variety_en)s,%(date)s,%(receipt)s,%(increase)s);",
            jsonable_encoder(receipt_item)
        )
    return {"message": "保存成功!", "save_count": save_count}


# --------------- 以上接口即将废弃
from db import FAConnection
from status import r_status

receipt_api = APIRouter()


@receipt_api.get('/hot/', summary='获取热门排序最新仓单')
async def get_hot_receipt():
    # 查询最新日期品种成交量记录并排序
    daily_quotes = 'SELECT variety_en,contract,trade_volume FROM dat_futures_daily_quotes ' \
                   'WHERE quotes_ts=(SELECT MAX(quotes_ts) FROM dat_futures_daily_quotes);'
    db = FAConnection()
    quotes = db.query(daily_quotes, keep_conn=True)
    quotes_df = pd.DataFrame(quotes)
    # 查询该日仓单数据,并进行统计排序
    receipt_sql = 'SELECT a.receipt_ts,a.variety_en,b.variety_name,a.warehouse,a.receipt ' \
                  'FROM dat_futures_daily_receipt AS a ' \
                  'INNER join sys_variety AS b ON a.variety_en=b.variety_en ' \
                  'WHERE a.ex_total=0 AND a.receipt_ts=(SELECT MAX(receipt_ts) FROM dat_futures_daily_receipt);'
    receipts = db.query(receipt_sql)
    receipt_df = pd.DataFrame(receipts)
    if quotes_df.empty or receipt_df.empty:
        return {'code': r_status.NOT_CONTENT, 'message': '没有数据!', 'data': []}
    receipt_date = datetime.fromtimestamp(receipts[0]['receipt_ts']).strftime('%Y-%m-%d')
    quotes_sum_df = quotes_df.groupby(by=['variety_en'], as_index=False)['trade_volume'].sum()
    receipt_sum_df = receipt_df.groupby(by=['variety_en', 'variety_name'], as_index=False)['receipt'].sum()
    receipt_sum_df['receipt_date'] = [receipt_date for _ in range(receipt_sum_df.shape[0])]

    # 对行情进行排序
    quotes_sum_df.sort_values(by=['trade_volume'], ascending=False, inplace=True)
    # 仓单合并
    ret_df = pd.merge(quotes_sum_df, receipt_sum_df, on=['variety_en'], how='inner')  # inner取交集
    # 整数化
    ret_df['receipt'] = ret_df['receipt'].apply(int)
    return {'code': r_status.SUCCESS, 'message': '获取热门品种仓单成功!', 'data': ret_df.to_dict(orient='records')}

